# -*- coding: utf-8 -*-
import sqlite3
import re

class DBOps:
	def __init__(self):
		self.conn = ""
		self.isTableCreated = False
		self.isConnected = False

	def connDB(self):
		if self.isConnected:
			raise Exception('An open connection exists!')
		self.conn = sqlite3.connect('test.db', timeout=1)
		self.conn.text_factory = str
		self.isConnected = True
		if not self.isTableCreated:
			self.createTable()
			self.isTableCreated = True

	def closeDB(self):
		if not self.isConnected:
			raise Exception('No open connection found!')
		self.conn.close()

	def insertSite(self, url):
		if not self.isConnected:
			raise Exception('No open connection found!')
		if not self.isTableCreated:
			self.createTable()
			self.isTableCreated = True
		if not self.isSiteExists(url):
			self.conn.execute("INSERT INTO pages (url) VALUES (?);", (url,))
			self.commitDB()

	def createTable(self):
		if not self.isConnected:
			raise Exception('No open connection found!')
		self.conn.execute('CREATE TABLE IF NOT EXISTS pages\
			(\
				_id INTEGER PRIMARY KEY AUTOINCREMENT,\
				url STRING\
			);')

	def dropTable(self):
		if not self.isConnected:
			raise Exception('No open connection found!')
		self.conn.execute('DROP TABLE pages')

	def commitDB(self):
		if not self.isConnected:
			raise Exception('No open connection found!')
		self.conn.commit()

	def isSiteExists(self, url):
		if not self.isConnected:
			raise Exception('No open connection found!')
		c = self.conn.cursor()
		c.execute("SELECT _id FROM pages WHERE url = ?", (url,))
		retV = True
		if c.fetchone() == None:
			retV = False
		c.close()
		return retV

	def getPages(self, start, count):
		if not self.isConnected:
			raise Exception('No open connection found!')
		c = self.conn.cursor()
		if start > -1 and count > -1:
			c.execute("SELECT url FROM pages LIMIT ?, ?", (start, count))
		elif start > -1:
			c.execute("SELECT url FROM pages LIMIT ?", (start,))
		else:
			c.execute("SELECT url FROM pages")
		rows = c.fetchall()
		c.close()
		return rows

	def getPageCount(self):
		if not self.isConnected:
			raise Exception('No open connection found!')
		c = self.conn.cursor()
		c.execute("SELECT count(_id) FROM pages")
		count = c.fetchone()[0]
		c.close()
		return count

